package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.dhcc.DRGsHosp.application.BenchmarkViewApplication;
@Named
@Transactional
public class BenchmarkViewApplicationImpl implements BenchmarkViewApplication {
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;
	@Override
	public Object getAllStdYear(String yearFlag,String groupVer,String verRegion,String theBench){
		String sql = "";
		if("1".equals(yearFlag)){
			sql="SELECT distinct anual.as_year FROM DRGS.T_ANNUALSTD ANUAL \n" + 
					" left join (select * from  DRGS.t_drgversion a where a.region_code='"+verRegion+"' and a.ver_code='"+groupVer+"') drgver on anual.ver_code=drgver.ver_code  \n" + 
					" where drgver.ver_code is not null \n";
		}else if("2".equals(yearFlag)){
			sql="SELECT DISTINCT M.MANAGE_YEAR AS_YEAR FROM DRGS.T_QUOTAMANAGE M where m.par_ms_code='"+theBench+"'";
		}else{
			sql="SELECT DISTINCT MD.STD_YEAR AS_YEAR FROM DRGS.T_MANAGESUMMARYDTL MD WHERE MD.PAR_MS_CODE='"+theBench+"'";
		}
		return jdbcTemplateObj.queryForList(sql);
		
	}
	@Override
	public List<Map<String, Object>> getBenchData(String theYear, String theBench,String theManageType,String groupVer,String verRegion){
		String sql="";
		if (theBench!=null && theBench.length()>0 && theBench.indexOf("manager_")==0){
			if("hosp".equals(theManageType)){
				sql="SELECT  M.PARQUOTA_CODE, CASE WHEN T.TEXT IS NOT NULL THEN T.TEXT \n";
				sql+="ELSE M.PARQUOTA_CODE END AS QUOTANAME,M.MANAGE_BENCHMARK,M.PATIENT_TYPE \n";
				sql+="FROM DRGS.T_QUOTAMANAGE M LEFT JOIN DRGS.T_QUOTASELECT T ON M.PARQUOTA_CODE=T.QUOTA_CODE \n";
				sql+="WHERE M.MANAGE_YEAR='"+theYear+"' AND M.PAR_MS_CODE='"+theBench+"' \n";
				sql+="AND M.PARDEP_CODE IS NULL AND M.PARDRG_CODE IS NULL";
			}else if("dep".equals(theManageType)){
				sql="SELECT * FROM (SELECT DEP.DEP_NAME,M.PARDEP_CODE,M.PATIENT_TYPE,M.MANAGE_BENCHMARK,M.PARQUOTA_CODE \n ";
				sql+="FROM DRGS.T_QUOTAMANAGE M LEFT JOIN DRGS.T_DEPARTMENT DEP \n";
				sql+="ON M.PARDEP_CODE=DEP.DEP_CODE WHERE  M.MANAGE_YEAR='"+theYear+"'AND M.PARDEP_CODE IS NOT NULL \n";
				sql+="AND M.PARDRG_CODE IS NULL AND M.PAR_MS_CODE='"+theBench+"' and dep.dep_name is not null and m.manage_benchmark >'0'  \n" ;
				sql+="and m.parquota_code in ('DrugRatio','AvgFee','AvgDays')) \n";
				sql+="PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
			}else{
				sql="SELECT * FROM (SELECT M.PARDRG_CODE,M.PARQUOTA_CODE,M.MANAGE_BENCHMARK,M.PATIENT_TYPE FROM DRGS.T_QUOTAMANAGE M \n";
				sql+="WHERE M.MANAGE_YEAR='"+theYear+"'AND M.PARDEP_CODE IS  NULL AND M.PARDRG_CODE IS NOT NULL AND M.PAR_MS_CODE='"+theBench+"') \n";
				sql+="PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
			}
		}else if(theBench!=null && theBench.length()>0 && theBench.indexOf("std_")==0){
			sql="  SELECT * FROM (SELECT MD.PARQUOTA_CODE,MD.MANAGE_BENCHMARK,MD.PARDRG_CODE FROM \n";
			sql+=" DRGS.T_MANAGESUMMARYDTL MD WHERE MD.STD_YEAR='"+theYear+"' AND MD.PAR_MS_CODE='"+theBench+"') \n";
			sql+=" PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
		}else if(theBench!=null && theBench.length()>0 && theBench.indexOf("ann")==0){
			sql = "SELECT anual.* FROM DRGS.T_ANNUALSTD ANUAL \n" + 
					" left join (select * from  DRGS.t_drgversion a where a.region_code='"+verRegion+"') drgver on anual.ver_code=drgver.ver_code  \n" + 
					" where drgver.ver_code is not null and drgver.ver_code='"+groupVer+"' and as_year='"+theYear+"' \n" ;
		}
		List<Map<String, Object>> benchDataMap = jdbcTemplateObj.queryForList(sql.toString());
		return benchDataMap;
	}
	/**
	 *  @Description：获取分组器版本号
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#getGroupVersion()
	 */
	@Override
	public List<Map<String, Object>>  getGroupVersion(){
		String sql = " select distinct a.ver_code,b.ver_desc  from drgs.t_annualstd a ";
		sql += " left join drgs.t_drgversion b on a.ver_code=b.ver_code ";
		sql += " where b.ver_code is not null ";
		sql += " order by ver_desc desc ";
		
		return jdbcTemplateObj.queryForList(sql);
		
	}
	/**
	 @description  ：获取标杆类型
	 *  @Author： fulianxia
	 *  @param type 类型
	 *  @param verCode 分组器版本号
	 *  @return
	 *  @Date  :2018年5月4日
	 */
	@Override
	public List<Map<String, Object>> findBenchType(String type,String verCode){
		String tableName = "";
		String columnName = "";
		String dicCode = "";
		String condition = "";
		if(StringUtils.hasLength(type) && "1".equals(type)){
			tableName = "DRGs.t_annualstd";
			columnName = "a.as_std_type";
			dicCode = "AS_STD_TYPE";
			if(StringUtils.hasLength(verCode)){
				condition = " and a.ver_code='"+verCode+"' ";
			}
		}else if(StringUtils.hasLength(type) && "2".equals(type)){
			tableName = "DRGs.t_quotamanage";
			columnName = "a.par_ms_code";
			dicCode = "MANAGE_TARGET";
			if(StringUtils.hasLength(verCode) && "hosp".equals(verCode)){
				condition = " and a.pardep_code is null and a.pardepgroup_code is null and a.pardrg_code is null ";
			}else if(StringUtils.hasLength(verCode) && "depGroup".equals(verCode)){
				condition = " and a.pardep_code is null and a.pardepgroup_code is not null and a.pardrg_code is null ";
			}else if(StringUtils.hasLength(verCode) && "dep".equals(verCode)){
				condition = " and a.pardep_code is not null and a.pardepgroup_code is null and a.pardrg_code is null ";
			}else if(StringUtils.hasLength(verCode) && "drg".equals(verCode)){
				condition = " and a.pardep_code is null and a.pardepgroup_code is null and a.pardrg_code is not null ";
			}else if(StringUtils.hasLength(verCode) && "depdrg".equals(verCode)){
				condition = " and a.pardep_code is not null and a.pardepgroup_code is null and a.pardrg_code is not null ";
			}
		}else if(StringUtils.hasLength(type) && "3".equals(type)){
			tableName = "DRGs.t_managesummary";
			columnName = "a.ms_code";
			dicCode = "STD_TARGET";
		}
		
		String sql=" select distinct dic_value,dic_desc from "+tableName+" a ";
		sql += " left join DRGs.mr_dictionarydata b on "+columnName+"=b.dic_value ";
		sql += " where dic_code='"+dicCode+"' ";
		if(StringUtils.hasLength(condition)){
			sql += condition;
		}
		sql += " order by dic_value ";
		return jdbcTemplateObj.queryForList(sql);
	}
	
	/**
	 * 
	 *  @Description：获取年度标杆年份
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#findYearBench()
	 */
	@Override
	public List<Map<String, Object>> findYearBench(String benchType,String verCode) {
		String sql = " select distinct a.as_year benchyear  from drgs.t_annualstd a where a.as_year is not null ";
		sql += " and a.as_std_type='"+benchType+"' and a.ver_code='"+verCode+"' order by as_year desc ";
		return jdbcTemplateObj.queryForList(sql);
	}
	/**
	 * 
	 *  @Description：获取管理目标值
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#findYearBench()
	 */
	@Override
	public List<Map<String, Object>> findManagerBench(String benchType,String verCode) {
		String sql = " select distinct a.manage_year benchyear  from drgs.t_quotamanage a where a.manage_year  is not null ";
		if(StringUtils.hasLength(verCode) && "hosp".equals(verCode)){
			sql += " and a.pardep_code is null and a.pardepgroup_code is null and a.pardrg_code is null";
		}else if(StringUtils.hasLength(verCode) && "depGroup".equals(verCode)){
			sql += " and a.pardep_code is null and a.pardepgroup_code is not null and a.pardrg_code is null";
		}else if(StringUtils.hasLength(verCode) && "dep".equals(verCode)){
			sql += " and a.pardep_code is not null and a.pardepgroup_code is null and a.pardrg_code is null";
		}else if(StringUtils.hasLength(verCode) && "drg".equals(verCode)){
			sql += " and a.pardep_code is null and a.pardepgroup_code is null and a.pardrg_code is not null";
		}else if(StringUtils.hasLength(verCode) && "depdrg".equals(verCode)){
			sql += " and a.pardep_code is not null and a.pardepgroup_code is null and a.pardrg_code is not null";
		}
		sql += " and a.par_ms_code='"+benchType+"'  order by manage_year desc ";
		return jdbcTemplateObj.queryForList(sql);
	}
	/**
	 * 
	 *  @Description：获取标杆比
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#findYearBench()
	 */
	@Override
	public List<Map<String, Object>> findStdBench(String benchType) {
		String sql = " select distinct a.std_year benchyear  from drgs.t_managesummarydtl a where a.std_year  is not null ";
		sql += " and a.par_ms_code='"+benchType+"'  order by benchyear desc ";
		return jdbcTemplateObj.queryForList(sql);
	}
	/**
	 * 
	 *  @Description：获取年度标杆
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#getStdBenchData(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getStdBenchData(String version, String stdYear, String stdType) {
		String sql="SELECT  a.*,d.drg_desc FROM DRGS.t_Annualstd a ";
		sql += " left join drgs.t_drgs d on a.as_drg_code=d.drg_code and a.ver_code=d.ver_code ";
		sql += " WHERE a.as_year='"+stdYear+"' and a.ver_code ='"+version+"' and a.as_std_type='"+stdType+"' ";
		return jdbcTemplateObj.queryForList(sql);
	}
	/**
	 * 
	 *  @Description：获取绩效标杆
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#getManagerBencahData(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getManagerBencahData(String theManageType, String theYear, String theBench) {
		String sql="";
		if("hosp".equals(theManageType)){
			sql="SELECT  M.PARQUOTA_CODE, CASE WHEN T.TEXT IS NOT NULL THEN T.TEXT \n";
			sql+="ELSE M.PARQUOTA_CODE END AS QUOTANAME,M.MANAGE_BENCHMARK,M.PATIENT_TYPE \n";
			sql+="FROM DRGS.T_QUOTAMANAGE M LEFT JOIN DRGS.T_QUOTASELECT T ON M.PARQUOTA_CODE=T.QUOTA_CODE \n";
			sql+="WHERE M.MANAGE_YEAR='"+theYear+"' AND M.PAR_MS_CODE='"+theBench+"' \n";
			sql+="AND M.PARDEP_CODE IS NULL AND M.PARDRG_CODE IS NULL  AND M.PARDEPGROUP_CODE IS NULL ";
		}else if("depGroup".equals(theManageType)){
			sql="SELECT * FROM (SELECT DEP.DEPGROUP_DESC OBJNAME,M.PARDEP_CODE,M.PATIENT_TYPE,M.MANAGE_BENCHMARK,M.PARQUOTA_CODE \n ";
			sql+="FROM DRGS.T_QUOTAMANAGE M LEFT JOIN DRGS.T_DEPGROUP DEP \n";
			sql+="ON M.PARDEPGROUP_CODE=DEP.DEPGROUP_CODE WHERE  M.MANAGE_YEAR='"+theYear+"'AND M.PARDEP_CODE IS  NULL AND M.PARDEPGROUP_CODE IS NOT  NULL \n";
			sql+="AND M.PARDRG_CODE IS NULL AND M.PAR_MS_CODE='"+theBench+"' and  m.PARDEPGROUP_CODE  is not null and m.manage_benchmark >'0'  \n" ;
			sql+="and m.parquota_code in ('DrugRatio','AvgFee','AvgDays')) \n";
			sql+="PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
		}else if("dep".equals(theManageType)){
			sql="SELECT * FROM (SELECT DEP.DEP_NAME OBJNAME,M.PARDEP_CODE,M.PATIENT_TYPE,M.MANAGE_BENCHMARK,M.PARQUOTA_CODE \n ";
			sql+="FROM DRGS.T_QUOTAMANAGE M LEFT JOIN DRGS.T_DEPARTMENT DEP \n";
			sql+="ON M.PARDEP_CODE=DEP.DEP_CODE WHERE  M.MANAGE_YEAR='"+theYear+"'AND M.PARDEP_CODE IS NOT NULL \n";
			sql+="AND M.PARDRG_CODE IS NULL AND M.PAR_MS_CODE='"+theBench+"' and m.PARDEP_CODE is not null and m.manage_benchmark >'0'  \n" ;
			sql+="and m.parquota_code in ('DrugRatio','AvgFee','AvgDays')) \n";
			sql+="PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
		}else{
			sql="SELECT * FROM (SELECT M.PARDRG_CODE OBJNAME,M.PARQUOTA_CODE,M.MANAGE_BENCHMARK,M.PATIENT_TYPE FROM DRGS.T_QUOTAMANAGE M \n";
			sql+="WHERE M.MANAGE_YEAR='"+theYear+"'AND M.PARDEP_CODE IS  NULL AND M.PARDRG_CODE IS NOT NULL AND M.PAR_MS_CODE='"+theBench+"') \n";
			sql+="PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
		}
		return jdbcTemplateObj.queryForList(sql);
	}
	/**
	 * 
	 *  @Description：
	 *  @Author liuhongjun 
	 *  @see com.dhcc.DRGsHosp.application.BenchmarkViewApplication#getBencahData(java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getBencahData(String theYear, String theBench) {
		String sql="  SELECT * FROM (SELECT md.pardrg_code drgcode, MD.PARQUOTA_CODE,MD.MANAGE_BENCHMARK,MD.PARDRG_CODE FROM \n";
		sql+=" DRGS.T_MANAGESUMMARYDTL MD WHERE MD.STD_YEAR='"+theYear+"' AND MD.PAR_MS_CODE='"+theBench+"') \n";
		sql+=" PIVOT (SUM(MANAGE_BENCHMARK) FOR PARQUOTA_CODE IN('DrugRatio' DrugRatio,'AvgFee' AvgFee,'AvgDays' AvgDays))";
	    return jdbcTemplateObj.queryForList(sql);
	}
}
